# -*- coding: utf -8 -*-
# autor: Rui Rebola e André Lascas
# data : 20/20/2014
# obs: primeira parte trabalho python

import xlrd
import sqlite3
import matplotlib.pyplot as plt
import numpy as np
import csv
import math

#from xlrd import open_workbook

"""
abertura do ficheiro excel
"""
wb = xlrd.open_workbook('IPC_Portugal_1977_2013.xls')
folha = wb.sheet_by_index(0)

"""
Criação dos arrays onde se irá guardar os dados
"""

IPC = []
Taxa_Variacao = []
rMinM = []
rMaxM = []
PIB = []
Rendimento_Nac = []
Rendimento_Disp = []
Renumeracoes = []
listadedados = []
	

def get_dados():

	"""
	Obtem os dados por linha e coloca os numa lista de listas
	"""	
	for row in range(folha.nrows-1):
		x = 0
		dados = []
		for col in range(folha.ncols-1):
			if type(folha.cell_value(row+1,col+1)) == float:						
				dados.append(round(folha.cell_value(row+1,col+1),1))										
			else:
				dados.append(folha.cell_value(row+1,col+1))
				#dados.append(0.0)
		x+=1				
		listadedados.append(dados)

	


######################################################################

"""
ligação a base de dados
"""
Connection = sqlite3.connect('IPC_POrtugal_1997_2013.db')
c = Connection.cursor()


"""
criação da tabela e
inserção de dados
"""

def create_table():
	c.execute('DROP TABLE IF EXISTS dados')
	c.execute('CREATE TABLE dados (Ano int, IPC float, VarAn float, rMinM float, rMaxM float, PIB float, RendNac float, RendDisp float, Renume float)')
	c.executemany('INSERT INTO dados VALUES (?,?,?,?,?,?,?,?,?)' , listadedados)
	Connection.commit()
	Connection.close()
	
	
# Create the graphics
def create_graph(data, title, y_title,x_title,isDist,k):
 
	values = []
	ano = []
	fig = plt.figure()
	ax = plt.subplot(111)
	width = 20

	for i in range(len(data)):
		if data[i][k] != '':
			values.append((data[i][k]))
			ano.append(int((data[i][0])))
			
	print values 
               
	Size = np.arange(len(values)) * width
	ax.bar(Size, values, width = width)
	ax.set_xticks(Size + width/2)
	ax.set_xticklabels(ano, rotation=90)
	ax.set_xlabel(x_title)
	ax.set_ylabel(y_title)
	ax.set_title(title)
        
        plt.grid(True)
        plt.show()
        
def average(values,k):
	'''
	length = len(values)	
	total = 0		
	for i in range ( length):
		total += values[i]	
	total =  total/ length
	'''
	arr =[]
	
	for i in range(len(values)):
		if values[i][k] != '':
			arr.append((values[i][k]))
			
	total = sum(arr)/len(arr)	
	return total

def standard_dev(values,k):
	arr =[]	
	
	for i in range(len(values)):
		if values[i][k] != '':
			arr.append((values[i][k]))
	


	length = len(arr)	
	av = average(values,k)	
	
	total = 0
	
	
	for i in range(length):
		total+= (arr[i]- av)**2
	
	
	x = total/(length-1)
		
	return math.sqrt(x)
	
	

get_dados()
print standard_dev(listadedados,1)
#print average(listadedados,1)
#create_table()
#create_graph(listadedados,"Estatistica" , "Valores", "Anos" , True,3)

	
